﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Pub;
using System.Data;
using System.Windows.Forms;
using LZQXT.Test.Forms;

namespace LZQXT.Test
{
    public static class CCreateGridII
    {

        static Dictionary<String, String> dicFieldType = new Dictionary<String, String>();


        /// <summary>
        /// 创建主表
        /// </summary>
        /// <param name="sID"></param>
        public static void creatTab(String sID)
        {
            CDebug.EnterMethod("开始创建主表，产品ID=" + sID);
            try
            {
                dicFieldType.Clear();
                dicFieldType.Add("整形", "int");
                dicFieldType.Add("日期", "datetime");
                dicFieldType.Add("字符串", "text");

                //创建主表
                createMainTab(sID);

                MessageBox.Show("主表创建成功！");
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                MessageBox.Show(ex.ToString());
            }


            CDebug.ExitMethod("创建主表结束");
        }

        public static void creatDetailTab(String sID)
        {
            CDebug.EnterMethod("开始创建从表，产品ID=" + sID);
            try
            {
                dicFieldType.Clear();
                dicFieldType.Add("整形", "int");
                dicFieldType.Add("日期", "datetime");
                dicFieldType.Add("字符串", "text");

                //创建从表
                createGridTab(sID);
                MessageBox.Show("从表创建成功！");
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                MessageBox.Show(ex.ToString());
            }


            CDebug.ExitMethod("创建从表结束");
        }

        /// <summary>
        /// 创建从表
        /// </summary>
        /// <param name="sID"></param>
        private static void createGridTab(String sID)
        {

            ADOTest ado3 = new ADOTest();
            ado3.S_Con = FDBTree_lzqx.ado1.S_Con;

            String strSQL1 = "select * from PF_PRODUCTCONFIG where pid=" + sID;
            DataSet ds1 = ado3.sel1(strSQL1);
            String sCust_Table = ds1.Tables[0].Rows[0]["Cust_DetailTable"].ToString();
            String strSQL2 = "SELECT Distinct(TableIndex) FROM PF_Product_GridPropert WHERE PID=" + sID + " ORDER BY TableIndex ASC";
            DataSet ds2 = ado3.sel1(strSQL2);
            if (ds2 == null || ds2.Tables.Count == 0) return;
            for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
            {
                String sTableIndex = ds2.Tables[0].Rows[i]["TableIndex"].ToString();
                DataSet ds3 = ado3.sel1("SELECT * FROM PF_Product_GridPropert WHERE PID=" + sID + " AND TableIndex=" + sTableIndex + " ORDER BY TableIndex ASC");
                String sTabName = sCust_Table + "_" + sTableIndex;

                String strSQL = "if not exists (select * from sysobjects where id = OBJECT_ID('[" + sTabName + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) "
                    + " CREATE TABLE [" + sTabName + "] (";

                if (!CPub.dsIsNull(ds3))
                {
                    foreach (DataRow dr in ds3.Tables[0].Rows)
                    {
                        if (dr["FieldName"].ToString().Trim().ToUpper() == "ID")
                            strSQL += " [" + dr["FieldName"].ToString() + "] " + getFileType(dr) + " NOT NULL ";
                        else
                            strSQL += ", [" + dr["FieldName"].ToString() + "] " + getFileType(dr) + " NULL ";
                    }
                }
                strSQL += ")";
                CDebug.Send(strSQL);
                ado3.cmd1(strSQL);

                string sField = "ID";
                strSQL = "ALTER TABLE [" + sTabName + "] ADD  CONSTRAINT [PK_" + sTabName + "] PRIMARY KEY CLUSTERED "
                   + "( [" + sField + "] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, "
                   + "IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
                ado3.cmd1(strSQL);
            }

            CDebug.Send("创建表成功！");

        }

        /// <summary>
        /// 创建主表
        /// </summary>
        /// <param name="sID"></param>
        private static void createMainTab(String sID)
        {

            ADOTest ado3 = new ADOTest();
            ado3.S_Con = FDBTree_lzqx.ado1.S_Con;

            DataSet ds1 = ado3.sel1("select * from PF_PRODUCTCONFIG where pid=" + sID);
            String sCust_Table = ds1.Tables[0].Rows[0]["Cust_Table"].ToString();

            DataSet ds2 = ado3.sel1("SELECT * FROM PF_Product_MainPropert WHERE IsSys=0 AND PID=" + sID);
            DataSet ds3 = ado3.sel1("SELECT * FROM PF_Product_MainPropert WHERE IsSys IN (1,3) AND PID=" + sID);
            String strSQL = "if not exists (select * from sysobjects where id = OBJECT_ID('[" + sCust_Table + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) "
                + " CREATE TABLE [" + sCust_Table + "] (             "
                + " [D_ID] [INT] NOT NULL,              "
                + " [Ddate] [datetime]  NULL,                   "
                + " [Issuer] [varchar]  (255) NOT NULL,         "
                + " [Nedition] [varchar]  (255) NOT NULL       ";
            if (!CPub.dsIsNull(ds3))
            {
                foreach (DataRow dr in ds3.Tables[0].Rows)
                {
                    strSQL += ", [" + dr["FieldName"].ToString() + "] [text] NULL";
                }
            }
            strSQL += ")";
            ado3.cmd1(strSQL);

            string sField = "ID";
            strSQL = "ALTER TABLE [" + sCust_Table + "] ADD  CONSTRAINT [PK_" + sCust_Table + "] PRIMARY KEY CLUSTERED "
               + "( [" + sField + "] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, "
               + "IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
            ado3.cmd1(strSQL);

            CDebug.Send(strSQL);
            CDebug.Send("创建表成功！");

        }

        private static String getFileType(DataRow dr)
        {
            String sTyp = dr["DataType"].ToString().Trim();
            String sLen = dr["Size"].ToString().Trim();
            sTyp = dicFieldType[sTyp].ToUpper();
            String sTem = "";
            switch (sTyp)
            {
                case "INT":
                    sTem = "[" + sTyp + "]";
                    break;
                case "DATATIME":
                    sTem = "[" + sTyp + "]";
                    break;
                case "VARCHAR":
                    sTem = "[" + sTyp + "] (" + sLen + ")";
                    break;
                case "TEXT":
                    sTem = "[" + sTyp + "]";
                    break;
            }

            return sTem;
        }


        public static void deleteTab(String sID)
        {

            ADOTest ado3 = new ADOTest();
            ado3.S_Con = FDBTree_lzqx.ado1.S_Con;
            DataSet ds1 = ado3.sel1("select * from PF_PRODUCTCONFIG where pid=" + sID);
            String sCust_Table = ds1.Tables[0].Rows[0]["Cust_Table"].ToString();

            string strSQL = "DROP TABLE " + sCust_Table;

            CDebug.Send(strSQL);
            ado3.cmd1(strSQL);

            MessageBox.Show("已删除 " + sCust_Table);

        }

        public static void deleteDetailTab(String sID)
        {
            ADOTest ado3 = new ADOTest();
            ado3.S_Con = FDBTree_lzqx.ado1.S_Con;
            String strSQL1 = "select * from PF_PRODUCTCONFIG where pid=" + sID;
            DataSet ds1 = ado3.sel1(strSQL1);
            String sCust_Table = ds1.Tables[0].Rows[0]["Cust_DetailTable"].ToString();
            String strSQL2 = "SELECT Distinct(TableIndex) FROM PF_Product_GridPropert WHERE PID=" + sID + " ORDER BY TableIndex ASC";
            DataSet ds2 = ado3.sel1(strSQL2);
            if (ds2 == null || ds2.Tables.Count == 0) return;

            string sTemTabNames = "";
            for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
            {
                String sTableIndex = ds2.Tables[0].Rows[i]["TableIndex"].ToString();

                String sTabName = sCust_Table + "_" + sTableIndex;

                String strSQL = "DROP TABLE [" + sTabName + "] ";
                sTemTabNames += sTabName + ", ";

                CDebug.Send(strSQL);
                ado3.cmd1(strSQL);

            }

            MessageBox.Show("已删除 " + sTemTabNames);
        }
    }
}